package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import pos.domain.Calle;
import pos.domain.Estacion;

public class JDBCEstacionDAO implements IEstacionDAO {
	
	Connection conn = ConnectionManager.getInstance().checkOut();
	
	public Estacion select(String nombreEstacion)
	{
		
		PreparedStatement stmt = null;
		ResultSet result = null;
		Estacion estacion = null;
		String sql = "SELECT * FROM estacion WHERE (nombre = ?) ";
		
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, nombreEstacion);
			result = stmt.executeQuery();

			result.next();
			estacion = new Estacion();
			estacion.setNumero(result.getInt("numero"));
			Calle calle = new JDBCCalleDAO().selectByID(result.getInt("idCalle"));
			estacion.setCalle(calle);
			estacion.setDescripcion(result.getString("descripcion"));

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
		return estacion;
	}
	
	
	public void insert(Estacion estacion) {
		String sql = "INSERT INTO estacionsevici (numero,idCalle,descripcion) VALUES (?,?,?) ";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, estacion.getNumero());
			stmt.setInt(2, estacion.getCalle().getIdCalle());
			stmt.setString(3, estacion.getDescripcion());
			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
	}


	@Override
	public List<Estacion> getEstacionesPorCalle(Calle calle) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		Estacion estacion = null;
		List<Estacion> lista = new ArrayList<Estacion>();
		String sql = "SELECT * FROM estacionsevici WHERE (idCalle = ?) ";
		
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, calle.getIdCalle());
			result = stmt.executeQuery();
			while(result.next()){
				estacion = new Estacion();
				estacion.setNumero(result.getInt("numero"));
				estacion.setCalle(calle);
				estacion.setDescripcion(result.getString("descripcion"));
				lista.add(estacion);
				result.next();
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
		return lista;
	}
}
